using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Collections;
using System.Data.SqlClient;


public class DBPrincipal : DALBase
{
    /**
     * Permite logearse al servidor
     */
    public static UsuarioBean getLoginWeb(string login, string clave)
    {
        UsuarioBean bean = null;

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {

            using (SqlCommand cmd = new SqlCommand("USPW_PRINCIPAL_S_LOGIN", conn))
            {
                conn.Open();
                cmd.Parameters.Add(new SqlParameter("@LOGIN", login));
                cmd.Parameters.Add(new SqlParameter("@CLAVE", clave.ToUpper()));

                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        bean = new UsuarioBean();

                        bean.id = loDr.GetValue(loDr.GetOrdinal("id")).ToString().Trim();

                        bean.codigo = loDr.GetValue(loDr.GetOrdinal("codigo")).ToString().Trim();

                        bean.nombre = loDr.GetValue(loDr.GetOrdinal("nombre")).ToString().Trim();

                        bean.clave = loDr.GetValue(loDr.GetOrdinal("clave")).ToString().Trim();

                        bean.login = loDr.GetValue(loDr.GetOrdinal("login")).ToString().Trim();

                        bean.codigoperfil = loDr.GetValue(loDr.GetOrdinal("codigoperfil")).ToString().Trim();

                        bean.estadoultimo = loDr.GetValue(loDr.GetOrdinal("estadoultimo")).ToString().Trim();

                        bean.nombre = loDr.GetValue(loDr.GetOrdinal("NOMBRE")).ToString().Trim();

                        bean.codigorol = loDr.GetValue(loDr.GetOrdinal("CODIGOROL")).ToString().Trim();
                    }
                }
            }
        }
        return bean;
    }

    // obtiene el avance del registro de actividades de un usuario
    public static AvanceBean obtenerAvance(AvanceBean avance)
    {
        AvanceBean avanceResultado = null;

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {

            using (SqlCommand cmd = new SqlCommand("USPW_PRINCIPAL_S_OBTENERAVANCE", conn))
            {
                conn.Open();
                cmd.Parameters.Add(new SqlParameter("@CODIGOUSUARIO", avance.codigoUsuario));
                cmd.Parameters.Add(new SqlParameter("@FECHAMOVIL", getStringFechaYYMMDDHHMM(avance.fechaMovil)));

                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        avanceResultado = new AvanceBean(avance.codigoUsuario, avance.fechaMovil);
                        avanceResultado.realizado = loDr.GetValue(loDr.GetOrdinal("REALIZADO")).ToString().Trim();
                        avanceResultado.programado = loDr.GetValue(loDr.GetOrdinal("PROGRAMADO")).ToString().Trim();
                    }
                }
            }
        }
        return avanceResultado;
    }

    // actualiza los datos del usuario
    public static String updateUserData(string login, string fechamovil, string ptn, string nextel, string ip)
    {
        String respuesta = "F";

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {

            using (SqlCommand cmd = new SqlCommand("USPW_PRINCIPAL_S_ACTUALIZARDATOS", conn))
            {
                conn.Open();
                cmd.Parameters.Add(new SqlParameter("@LOGIN", login));
                cmd.Parameters.Add(new SqlParameter("@FECHAMOVIL", getStringFechaYYMMDDHHMM(fechamovil)));
                cmd.Parameters.Add(new SqlParameter("@TELEFONO", ptn));
                cmd.Parameters.Add(new SqlParameter("@NEXTEL", nextel));
                cmd.Parameters.Add(new SqlParameter("@IP", ip));

                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        respuesta = loDr.GetValue(loDr.GetOrdinal("ACTUALIZA")).ToString().Trim();
                    }
                }
            }
        }
        return respuesta;
    }

    /**
     * Obtiene la configuracion del sistema
     */
    public static void getConfiguracion()
    {


        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPW_PRINCIPAL_S_CONFIGURACION", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {

                        ManagerConfiguration.motivoGeneral = loDr.GetValue(loDr.GetOrdinal("motivo")).ToString().Trim();
                        ManagerConfiguration.gps = loDr.GetValue(loDr.GetOrdinal("gps")).ToString().Trim();
                        ManagerConfiguration.ruta = loDr.GetValue(loDr.GetOrdinal("ruta")).ToString().Trim();
                        ManagerConfiguration.foto = loDr.GetValue(loDr.GetOrdinal("foto")).ToString().Trim();
                        ManagerConfiguration.formatofecha = loDr.GetValue(loDr.GetOrdinal("fecha")).ToString().Trim();
                        ManagerConfiguration.idioma = loDr.GetValue(loDr.GetOrdinal("culture")).ToString().Trim();
                        ManagerConfiguration.signo = loDr.GetValue(loDr.GetOrdinal("signo")).ToString().Trim();
                        ManagerConfiguration.laltitudlongitud = loDr.GetValue(loDr.GetOrdinal("latlon")).ToString().Trim();

                    }
                }
            }
        }

    }

    public static Dictionary<string, string> getCultureIdioma(string tipo)
    {

        Dictionary<string, string> datos = new Dictionary<string, string>();
        String CULTURE = "";
        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPW_PRINCIPAL_S_IDIOMA", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@TIPO", fnVerNuloStr(tipo)));

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        CULTURE = loDr.GetValue(loDr.GetOrdinal("CULTURE")).ToString().Trim();
                        datos.Add(loDr.GetValue(loDr.GetOrdinal("CODIGO")).ToString().Trim(),
                        loDr.GetValue(loDr.GetOrdinal("DESCRIPCION")).ToString().Trim());
                    }

                    datos.Add(IdiomaCultura.CULTURE, CULTURE);

                }
            }
        }

        return datos;
    }

    public static List<ComboBean> getPais()
    {
        List<ComboBean> lstPais = new List<ComboBean>();

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPW_PRINCIPAL_S_PAIS", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        ComboBean bean = new ComboBean();
                        bean.codigo = loDr.GetValue(loDr.GetOrdinal("CODIGO")).ToString().Trim().ToUpper();
                        bean.nombre = loDr.GetValue(loDr.GetOrdinal("NOMBRE")).ToString().Trim().ToUpper();
                        lstPais.Add(bean);
                    }

                }
            }
        }


        return lstPais;
    }


    public static List<IdiomaBean> getCultureIdiomaLista(string tipo)
    {

        List<IdiomaBean> datos = new List<IdiomaBean>();

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPW_PRINCIPAL_S_IDIOMA", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@TIPO", fnVerNuloStr(tipo)));


                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        IdiomaBean bean = new IdiomaBean();
                        bean.id = loDr.GetValue(loDr.GetOrdinal("CODIGO")).ToString().Trim().ToUpper();
                        bean.valor = loDr.GetValue(loDr.GetOrdinal("DESCRIPCION")).ToString().Trim().ToUpper().Replace("\\N", "\n");
                        datos.Add(bean);
                    }

                }
            }
        }

        return datos;
    }



    public static MotivoBean getMotivoBeanPK(String id)
    {

        MotivoBean bean = null;

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {

            using (SqlCommand cmd = new SqlCommand("USPW_MAESTRO_S_PK", conn))
            {

                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ID", id));

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        bean = new MotivoBean();
                        bean.grupo = loDr.GetValue(loDr.GetOrdinal("GRUPO")).ToString().Trim();

                        bean.codigo = loDr.GetValue(loDr.GetOrdinal("CODIGO")).ToString().Trim();

                        bean.valor = loDr.GetValue(loDr.GetOrdinal("VALOR")).ToString().Trim();

                        bean.nombre = loDr.GetValue(loDr.GetOrdinal("NOMBRE")).ToString().Trim();

                        bean.flag = loDr.GetValue(loDr.GetOrdinal("FLAG")).ToString().Trim();

                        bean.id = loDr.GetValue(loDr.GetOrdinal("ID")).ToString().Trim();

                    }
                }
            }
        }

        return bean;
    }

    public static void addMotivoBean(MotivoBean beanParametro)
    {
        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPW_MAESTRO_I", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@GRUPO", fnVerNuloStr(beanParametro.grupo)));

                cmd.Parameters.Add(new SqlParameter("@CODIGO", fnVerNuloStr(beanParametro.codigo)));

                cmd.Parameters.Add(new SqlParameter("@VALOR", fnVerNuloStr(beanParametro.valor)));

                cmd.Parameters.Add(new SqlParameter("@NOMBRE", fnVerNuloStr(beanParametro.nombre)));

                cmd.Parameters.Add(new SqlParameter("@FLAG", fnVerNuloStr(beanParametro.flag)));

                cmd.Parameters.Add(new SqlParameter("@ID", fnVerNuloStr(beanParametro.id)));

                cmd.ExecuteNonQuery();

            }
        }

    }

    public static List<AbstractBean> findMotivoBean(MotivoBean beanParametro)
    {
        List<AbstractBean> lista = new List<AbstractBean>();
        MotivoBean bean = null;


        /**********************************************************************/
        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {

            using (SqlCommand cmd = new SqlCommand("USPW_MAESTRO_S_BUSQUEDA", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@GRUPO", fnVerNuloStr(beanParametro.grupo)));

                cmd.Parameters.Add(new SqlParameter("@CODIGO", fnVerNuloStr(beanParametro.codigo)));

                cmd.Parameters.Add(new SqlParameter("@NOMBRE", fnVerNuloStr(beanParametro.nombre)));

                cmd.Parameters.Add(new SqlParameter("@FLAG", fnVerNuloStr(beanParametro.flag)));

                cmd.Parameters.Add(new SqlParameter("@PAGINAACTUAL", beanParametro.paginaactual));

                cmd.Parameters.Add(new SqlParameter("@REGISTROPORPAGINA", beanParametro.registroporpagina));

                cmd.Parameters.Add(new SqlParameter("@BUSCARNAME", fnVerNuloStr(beanParametro.buscarname)));

                cmd.Parameters.Add(new SqlParameter("@BUSCARORDEN", beanParametro.buscarorden));


                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        bean = new MotivoBean();
                        bean.grupo = loDr.GetValue(loDr.GetOrdinal("GRUPO")).ToString().Trim();

                        bean.codigo = loDr.GetValue(loDr.GetOrdinal("CODIGO")).ToString().Trim();

                        bean.valor = loDr.GetValue(loDr.GetOrdinal("VALOR")).ToString().Trim();

                        bean.nombre = loDr.GetValue(loDr.GetOrdinal("NOMBRE")).ToString().Trim();

                        bean.id = loDr.GetValue(loDr.GetOrdinal("ID")).ToString().Trim();

                        bean.tamaniototal = loDr.GetValue(loDr.GetOrdinal("TAMANIOTOTAL")).ToString().Trim();

                        lista.Add(bean);
                    }
                }
            }
        }

        return lista;
    }

    public static List<ParametroBean> getParametrosActividad()
    {
        List<ParametroBean> lista = new List<ParametroBean>();
        ParametroBean bean = null;

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPW_PARAMETRO_S_ACTIVIDAD", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        bean = new ParametroBean();
                        bean.idParametro = loDr.GetInt64(loDr.GetOrdinal("IDPARAMETRO"));
                        bean.descripcion = loDr.GetValue(loDr.GetOrdinal("DESCRIPCION")).ToString().Trim();
                        bean.valor = loDr.GetDouble(loDr.GetOrdinal("VALOR"));
                        bean.color = loDr.GetValue(loDr.GetOrdinal("COLOR")).ToString().Trim();

                        lista.Add(bean);
                    }
                }
            }
        }

        return lista;
    }

    public static ParametroBean getOneParametro(int PAR_PK)
    {
        ParametroBean bean = null;

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPW_PARAMETRO_S_O", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PAR_PK", PAR_PK));

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        bean = new ParametroBean();
                        bean.idParametro = loDr.GetInt64(loDr.GetOrdinal("IDPARAMETRO"));
                        bean.descripcion = loDr.GetValue(loDr.GetOrdinal("DESCRIPCION")).ToString().Trim();
                        bean.valor = loDr.GetDouble(loDr.GetOrdinal("VALOR"));
                        bean.color = loDr.GetValue(loDr.GetOrdinal("COLOR")).ToString().Trim();
                    }
                }
            }
        }

        return bean;
    }

    public static Boolean updateParametro(int pk, String descripcion, String valor, String color)
    {

        bool resultado = true;
        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPW_PARAMETRO_U", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@PK", pk));
                cmd.Parameters.Add(new SqlParameter("@DESCRIPCION", descripcion.Trim()));
                cmd.Parameters.Add(new SqlParameter("@VALOR", valor.Trim()));
                cmd.Parameters.Add(new SqlParameter("@COLOR", color.Trim()));
                cmd.ExecuteNonQuery();


            }
        }
        return resultado;
    }

    public static Boolean actualizarConfiguracion(int motivo, int gps, int ruta, int foto, string culture, int fecha, string signo)
    {
        bool resultado = true;
        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPW_PRINCIPAL_U_CONFIGURACION", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@MOTIVO", motivo));
                cmd.Parameters.Add(new SqlParameter("@GPS", gps));
                cmd.Parameters.Add(new SqlParameter("@RUTA", ruta));
                cmd.Parameters.Add(new SqlParameter("@FOTO", foto));
                cmd.Parameters.Add(new SqlParameter("@CULTURE", culture.Trim()));
                cmd.Parameters.Add(new SqlParameter("@FECHA", fecha));
                cmd.Parameters.Add(new SqlParameter("@SIGNO", signo.Trim()));

                cmd.ExecuteNonQuery();


            }
        }
        return resultado;
    }


    public static bool validarHoyPasoPorWebDatamart(String codigousuario, String telefono, String fechaMovil)
    {
        bool resultado = false;
        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPD_DATAMART_VALIDASERVICIOACTIVO", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@CODIGOUSUARIO", codigousuario));
                cmd.Parameters.Add(new SqlParameter("@FECHAMOVIL", getStringFechaYYMMDDHHMM(fechaMovil)));
                cmd.Parameters.Add(new SqlParameter("@PTN", telefono));

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        Int64 i = Int64.Parse(loDr.GetValue(loDr.GetOrdinal("VALOR")).ToString().Trim());
                        if (i > 0)
                        {
                            resultado = true;
                        }
                    }
                }
            }
        }
        return resultado;
    }

    public static void actualizarFechaDataMart(String codigousuario)
    {
        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USPD_DATAMART_ACTUALIZAFECHA", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@CODIGOUSUARIO", codigousuario));

                cmd.ExecuteNonQuery();

            }
        }
    }



    public static List<BEGenGrafico> getActProgramdas(String fechainicio, String fechafin, String User)
    {

        List<BEGenGrafico> lista = new List<BEGenGrafico>();

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USP_PER_GRAFICA_ACTIVIDADES", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@FechInicio", fnVerNuloStr(fechainicio)));
                cmd.Parameters.Add(new SqlParameter("@FechFin", fnVerNuloStr(fechafin)));
                cmd.Parameters.Add(new SqlParameter("@User", fnVerNuloStr(User)));
                cmd.Parameters.Add(new SqlParameter("@FLAG_SUPERVISOR", fnVerNuloStr(AbstractBean.ACTIVO)));

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        BEGenGrafico bean = new BEGenGrafico();
                        bean.realizado = (String)loDr.GetValue(loDr.GetOrdinal("realizado")).ToString();
                        bean.programado = (String)loDr.GetValue(loDr.GetOrdinal("programado")).ToString();
                        lista.Add(bean);
                    }
                }
            }
        }

        return lista;
    }

    public static List<BEGenGrafico> getNoVisitasRea(String fechainicio, String fechafin, String User)
    {
        List<BEGenGrafico> lista = new List<BEGenGrafico>();

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USP_PER_GRAFICA_NOVISITA", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@FechInicio", fnVerNuloStr(fechainicio)));
                cmd.Parameters.Add(new SqlParameter("@FechFin", fnVerNuloStr(fechafin)));
                cmd.Parameters.Add(new SqlParameter("@User", fnVerNuloStr(User)));
                cmd.Parameters.Add(new SqlParameter("@FLAG_SUPERVISOR", fnVerNuloStr(AbstractBean.ACTIVO)));

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        BEGenGrafico bean = new BEGenGrafico();
                        bean.novisita = (String)loDr.GetValue(loDr.GetOrdinal("novisita")).ToString();
                        bean.programado = (String)loDr.GetValue(loDr.GetOrdinal("programado")).ToString();
                        lista.Add(bean);
                    }
                }
            }
        }

        return lista;
    }

    public static void getSupervisor(String usuario)
    {
        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USP_SEL_SUPERVISOR", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Usuario", fnVerNuloStr(usuario)));
                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        AbstractBean.Super = (String)loDr.GetValue(loDr.GetOrdinal("supervisor")).ToString();
                    }
                }
            }
        }
    }

    public static List<BEGenGrafico> getActividadesRealizadas(String fechainicio, String fechafin, String Usuario, String Grafico)
    {
        List<BEGenGrafico> lista = new List<BEGenGrafico>();

        using (SqlConnection conn = new SqlConnection(prCadConexion))
        {
            using (SqlCommand cmd = new SqlCommand("USP_CHART_MONITOREO_ACTIVIDADESREALIZADAS", conn))
            {
                conn.Open();
                cmd.CommandTimeout = 0;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@FECHA_INICIO", fnVerNuloStr(fechainicio)));
                cmd.Parameters.Add(new SqlParameter("@FECHA_FIN", fnVerNuloStr(fechafin)));
                cmd.Parameters.Add(new SqlParameter("@IDUSUARIO", fnVerNuloStr(Usuario)));
                cmd.Parameters.Add(new SqlParameter("@FLAG_SUPERVISOR", fnVerNuloStr(AbstractBean.ACTIVO)));

                using (SqlDataReader loDr = cmd.ExecuteReader())
                {
                    while (loDr.Read())
                    {
                        BEGenGrafico bean = new BEGenGrafico();
                        bean.Total = (String)loDr.GetValue(loDr.GetOrdinal("Total")).ToString();
                        bean.usuario = (String)loDr.GetValue(loDr.GetOrdinal("usuario")).ToString();
                        bean.avrusuario = (String)loDr.GetValue(loDr.GetOrdinal("avrusuario")).ToString();
                        lista.Add(bean);
                    }
                }
            }
        }

        return lista;
    }
}